#########################
#   Author : Leon yu    #
#   Date : 2025/06/05   #
#   Id : SM2772         #
#########################

# 报表-营业概览
from datetime import datetime
import pandas as pd
from dbResps import dc
from dbResps import storeId, Log
from bussinessApis import isNowDate


def getBusinessOverviewFromDB(startTime:int, endTime:int):
    compareIsNowDate = isNowDate(startTime, endTime)
    if compareIsNowDate != 0:
        Log.error('startTime or endTime input Wrong!')
        return
    
    businessOverview = dict()
    startTime2Date = dc.timestamp2Data(startTime)
    endTime2Date = dc.timestamp2Data(endTime)
    tableName = 'order'
    where = f'store_id = "{storeId}" and `status` = \"SETTLED\" and (settle_time between "{startTime2Date}" and "{endTime2Date}")'
    resp = dc.select(tableName=tableName, where=where)
    df = pd.DataFrame(resp)
    # 订单数
    orderCount = 0
    # 营业收入
    payableAmountSum = 0
    # 商品数量
    skuCount = 0
    # 毛利润 
    grossProfit = 0
    def getSkuCount(dataframe:pd.DataFrame):
         # 获取订单列表，查询order_detail
        orderTuple = tuple(dataframe.drop_duplicates(subset=['order_id'])['order_id'].tolist())
        orderTuple = dc.tuple2Str(orderTuple)
        # if len(orderTuple) == 1:
        #     orderTuple = f"({orderTuple[0]})"
        tableName = "order_detail"
        columns="quantity/1000 as quantity"
        where = f"order_id in {orderTuple} and store_id = \"{storeId}\" and is_refund is not true"
        resp = dc.select(tableName=tableName, columns=columns, where=where)
        skuCount = sum([int(i['quantity']) for i in resp])
        return skuCount
    def getGrossProfit(dataframe:pd.DataFrame):
        # 获取订单列表，查询order_detail
        orderTuple = tuple(dataframe.drop_duplicates(subset=['order_id'])['order_id'].tolist())
        if len(orderTuple) == 1:
            orderTuple = f"({orderTuple[0]})"
        tableName = "order_detail"
        # columns = "sum(actual_amount-cost_price) as grossProfit"
        columns = "sum((actual_amount-cost_price)*quantity/1000) as grossProfit"
        where = f"order_id in {orderTuple} and store_id = \"{storeId}\" and is_refund is not true"
        resp = dc.select(tableName=tableName, columns=columns, where=where)
        grossProfit = sum([int(i['grossProfit']) for i in resp])
        return grossProfit
    if df.shape[0] > 0:
        orderCount = df.shape[0]
        payableAmountSum = df['payable_amount'].sum()     
        skuCount = getSkuCount(df)
        grossProfit = getGrossProfit(df)

    businessOverview['TURNOVER'] = payableAmountSum
    businessOverview['ORDERNUMS'] = orderCount
    businessOverview['ITEMNUMS'] = skuCount
    businessOverview['GROSS'] = grossProfit
    return businessOverview
    
    
def getHistoryBusinessOverviewFromDB(startTime:int, endTime:int=0):
    compareIsNowDate = isNowDate(startTime, endTime)
    if compareIsNowDate != 1:
        Log.error('startTime or endTime input Wrong, because not today')
        return
    
     # 订单数
    orderCount = 0
    # 营业收入
    payableAmountSum = 0
    # 商品数量
    skuCount = 0
    # 毛利润 
    grossProfit = 0
    
    businessOverview = dict()
    startTime2Date = datetime.strptime(dc.timestamp2Data(startTime), "%Y/%m/%d %H:%M:%S").strftime('%Y-%m-%d %H:%M:%S')
    endTime2Date = datetime.strptime(dc.timestamp2Data(endTime), "%Y/%m/%d %H:%M:%S").strftime('%Y-%m-%d %H:%M:%S')
    tableName = 'order_overview_report_day'
    # where = f'store_id = "{storeId}" and report_day like "{startTime2Date}%"'
    where = f'store_id = "{storeId}" and report_day between \"{startTime2Date}\" and \"{endTime2Date}\"'
    resp = dc.select(tableName=tableName, where=where)
    df = pd.DataFrame(resp)
    def getSkuCount():
        tableName = 'bussiness_trend_report'
        columns = "sum(item_quantity/1000) as item_quantity"
        # where = f'store_id = "{storeId}" and report_day like "{startTime2Date}%"'
        where = f'store_id = "{storeId}" and report_day between \"{startTime2Date}\" and \"{endTime2Date}\"'
        df = pd.DataFrame(dc.select(tableName=tableName, where=where, columns=columns))
        return int(df['item_quantity'].sum())
    def getGrossProfit():
        tableName = "bussiness_trend_report"
        columns = "sum(json_Extract(total_gross, '$.amount')) as amount"
        # where = f'store_id = "{storeId}" and report_day like "{startTime2Date}%"'
        where = f'store_id = "{storeId}" and report_day between \"{startTime2Date}\" and \"{endTime2Date}\"'
        df = pd.DataFrame(dc.select(tableName=tableName, columns=columns, where=where))
        return int(df['amount'].sum())
    
    if df.shape[0] > 0:
        orderCount = df['settled_count'].sum()
        payableAmountSum = df['payable_amount'].sum()
        skuCount = getSkuCount()
        grossProfit = getGrossProfit()
    
    # print(df)
    businessOverview['TURNOVER'] = payableAmountSum
    businessOverview['ORDERNUMS'] = orderCount
    businessOverview['ITEMNUMS'] = skuCount
    businessOverview['GROSS'] = grossProfit
    return businessOverview
    
    
# startTime = 1717434000000
# endTime = 1717606799000
# print(getHistoryBusinessOverviewFromDB(startTime, endTime))
        

